package com.wxxymaker.service;

import com.wxxymaker.model.entity.Stage;
import com.wxxymaker.model.entity.StageCount;
import com.wxxymaker.model.entity.User;
import com.wxxymaker.utils.FileUtil;
import com.wxxymaker.utils.WorkBookUtil;
import jdk.nashorn.internal.ir.annotations.Ignore;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: Soft
 * @Date: 2018/5/14 22:31
 * @Desc: Excel业务层
 */
@Service
public class ExcelService {
    private final static Logger log = LoggerFactory.getLogger(ExcelService.class);

    private DecimalFormat numberFormat = new DecimalFormat("0.00");
    private DateFormat    dateFormat   = new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss");

    private final StageService stageService;

    private final StageCountService stageCountService;

    private final UserService userService;

    @Autowired
    public ExcelService(UserService userService, StageCountService stageCountService, StageService stageService) {
        this.userService = userService;
        this.stageCountService = stageCountService;
        this.stageService = stageService;
    }

    /**
     * 导入用户信息
     * @param input 用户信息excel表格，信息包括
     *                  工号(账号,学号) 姓名 职位 :必须  电话 邮箱 :可有可无
     */
    public void inputUser(File input) throws IOException, BiffException {
        Workbook workbook = Workbook.getWorkbook(input);
        Sheet sheet = workbook.getSheet(0);

        Cell[] title = sheet.getRow(0); // 先读标题
        Map<String, Integer> titleMapping = new HashMap<>();
        for (int i = 0; i < title.length; i++) {
            titleMapping.put(title[i].getContents(), i);
        }

        for (int i = 1; i < sheet.getRows(); i++) { // 读取内容 这时不要标题
            User user = new User();

            Cell[] content = sheet.getRow(i);
            String username = "";
            if (titleMapping.containsKey("工号")) {
                username = content[titleMapping.get("工号")].getContents();
            } else if (titleMapping.containsKey("学号")) {
                username = content[titleMapping.get("学号")].getContents();
            } else {
                username = content[titleMapping.get("账号")].getContents();
            }

            String post = null;
            try {
                post = content[titleMapping.get("职位")].getContents();
            } catch (Exception e) {
                log.error("本列内容读取异常 ArrayIndex: {}", e.getMessage());
                post = "";
            }
            // 从第一行数据读取职位信息
            if (post == null ||  "".equals(post)) {
                Cell[] row = sheet.getRow(1);
                post = row[titleMapping.get("职位")].getContents();
            }

            try{
                // 跳过空白账号
                if (username == null || "".equals(username) || username.length() < 1) {
                    continue;
                }
                // 过滤掉已经毕业的学生
                LocalDate now   = LocalDate.now();
                Integer   year  = Integer.parseInt(username.substring(0, 4));
                Integer   cy    = now.getYear() - year;
                Integer   month = now.getMonthValue();
                if (!"教师".equals(post) && (cy > 4 || (cy == 4 && month > 4))) {
                    continue;
                }
            } catch (Exception e) {
                log.error("这是个老师，截取工号错误！{}", username);
            }

            user.setUsername(username);
            user.setPassword(username);
            user.setRealName(content[titleMapping.get("姓名")].getContents());

            user.setPost("教师".equals(post) ? User.PostDim.TEA : User.PostDim.STU);

            if (titleMapping.containsKey("电话")) {
                user.setTel(content[titleMapping.get("电话")].getContents());
            }

            if (titleMapping.containsKey("邮箱")) {
                user.setEmail(content[titleMapping.get("邮箱")].getContents());
            }

            // 测试的。。。没用到
            if (titleMapping.containsKey("性别")) {
                user.setSex(content[titleMapping.get("性别")].getContents());
            }

            try {
                userService.deleteByUsername(user.getUsername()); // 先删除 在添加 防止重复
                userService.add(user);
            } catch (Exception e) {
                log.debug("{}", e);
                log.error("导入用户<{}>出错--> {}", user.getRealName(), e.getMessage());
            }
        }

        workbook.close();
    }

    public void outputUser(File output) throws IOException, WriteException {
        this.outputUser(null, output);
    }

    /**
     * 将用户信息导出成Excel表格
     * @param post   用户查询条件， null | 0 为全部
     * @param output 输出到的目标文件
     */
    public void outputUser(Integer post, File output) throws IOException, WriteException {
        FileUtil.createFolder(output);

        Map<String, Object> map = new HashMap<>();
        if (post != null && post > 0) {
            map.put("post", post);
        }
        List<User> users = userService.page(1, Integer.MAX_VALUE, map).getContent();

        WritableWorkbook workbook = Workbook.createWorkbook(output);

        WritableSheet sheet = workbook.createSheet("用户信息", 0);
        sheet.addCell(new Label(0, 0, "用户信息表", WorkBookUtil.getHeaderStyle()));
        sheet.mergeCells(0, 0, 7, 0);

        sheet.setRowView(0, 480);
        sheet.setColumnView(1, 15);
        sheet.setColumnView(4, 15);
        sheet.setColumnView(5, 20);
        sheet.setColumnView(6, 22);
        // 设置标题
        String[] titles = {"序号", "账号", "姓名", "职位", "电话", "邮箱", "注册日期", "参评次数"};
        for (int i = 0; i < titles.length; i++) {
            WritableCell writableCell = new Label(i, 1, titles[i], WorkBookUtil.getTitleStyle());
            sheet.addCell(writableCell);
        }

        // 导出数据
        for (int i = 2; i < users.size() + 2; i++) {
            User user = users.get(i - 2);
            sheet.addCell(new Label(0, i, "" + (i - 1)));
            sheet.addCell(new Label(1, i, user.getUsername()));
            sheet.addCell(new Label(2, i, user.getRealName()));
            sheet.addCell(new Label(3, i, user.getPost() == User.PostDim.TEA ? "教师":"学生"));
            sheet.addCell(new Label(4, i, user.getTel()));
            sheet.addCell(new Label(5, i, user.getEmail()));
            sheet.addCell(new Label(6, i, String.valueOf(user.getRegTime())));
            sheet.addCell(new Label(7, i, String.valueOf(user.getNum())));
        }

        sheet.mergeCells(0, users.size() + 2, 7, users.size() + 2);
        sheet.addCell(WorkBookUtil.getDefaultCell(0, users.size() + 2,
                "表格生成时间：" + dateFormat.format(new Date())));
        sheet.setRowView(users.size() + 2, 400);
        workbook.write();
        workbook.close();
    }

    public void outputStageCount(File output) throws IOException, WriteException {
        this.outputStageCount(output, null, null, true);
    }
    /**
     * 将阶段统计信息导出
     * @param output  导出文件
     * @param stageId 指定的导出阶段
     * @param shopId  指定的公司id
     * @param detail  是否显示详细内容 true显示
     *                详细内容就是包括每个等级的参评人数 和 总参评人数
     */
    public void outputStageCount(File output, Integer stageId, Integer shopId, boolean detail) throws IOException, WriteException {
        FileUtil.createFolder(output);

        String stageName = "全部阶段打分 统计表";
        Map<String, Object> map = new HashMap<>();
        if (null != stageId && stageId > 0) {
            map.put("stage_id", stageId);
            Stage stage = stageService.getOne(stageId);
            if (stage != null) {
                stageName = stage.getName() + " 统计表";
            }
        }

        if (shopId != null && shopId > 0) {
            map.put("shop_id", shopId);
        }

        Sort sort = new Sort(Sort.Direction.DESC, "total");
        List<StageCount> stageCounts = stageCountService.page(1, Integer.MAX_VALUE, map, sort).getContent();

        WritableWorkbook workbook = Workbook.createWorkbook(output);

        WritableSheet sheet = workbook.createSheet("打分阶段统计表", 0);
        sheet.addCell(new Label(0, 0, stageName, WorkBookUtil.getHeaderStyle()));

        // 设置标题
        String[] titles = null;
        if (detail) {
            titles = new String[]{"序号", "公司名", "打分阶段", "参评总人数",
                    "优秀人数", "良好人数", "合格人数", "差评人数", "总评分"};
        } else {
            titles = new String[]{"序号", "公司名", "打分阶段", "总评分"};
        }

        // 顶部合并单元格
        sheet.mergeCells(0, 0, titles.length - 1, 0);

        sheet.setRowView(0, 480);
        sheet.setColumnView(1, 12);
        sheet.setColumnView(2, 25);
        sheet.setColumnView(3, 12);

        for (int i = 0; i < titles.length; i++) {
            WritableCell writableCell = new Label(i, 1, titles[i], WorkBookUtil.getTitleStyle());
            sheet.addCell(writableCell);
        }

        for (int i = 2; i < stageCounts.size() + 2; i++) {
            StageCount stageCount = stageCounts.get(i - 2);
            sheet.addCell(new Label(0, i, "" + (i - 1)));
            sheet.addCell(new Label(1, i, stageCount.getShop().getName()));
            sheet.addCell(new Label(2, i, stageCount.getStage().getName()));
            if (detail) {
                long[] longs = stageService.countStarNumForShop(stageCount.getStage(), stageCount.getShop());
                sheet.addCell(new Label(3, i, ""+(longs[0] + longs[1] + longs[2] + longs[3])));
                sheet.addCell(new Label(4, i, longs[3] + ""));
                sheet.addCell(new Label(5, i, longs[2] + ""));
                sheet.addCell(new Label(6, i, longs[1] + ""));
                sheet.addCell(new Label(7, i, longs[0] + ""));
            }
            sheet.addCell(new Label(titles.length - 1, i, String.valueOf(stageCount.getTotal())));
        }

        // 底部合并单元格
        sheet.mergeCells(0, stageCounts.size() + 2, titles.length - 1, stageCounts.size() + 2);
        sheet.addCell(WorkBookUtil.getDefaultCell(0, stageCounts.size() + 2,
                "表格生成时间：" + dateFormat.format(new Date())));
        sheet.setRowView(stageCounts.size() + 2, 400);
        workbook.write();
        workbook.close();
    }
}
